using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace eyeArchive.Core
{
	public class Table
	{
		#region "Members"
		private string primaryTable;
		private string primarySchema;
		private string foreignTable;
		private string foreignSchema;
		private Kernel parent;
		private List<Column> columns;
		private List<Column> droppedColumns;
		//private List<Dependency> dependencies;
		private string condition;
		private Statuses status;
		private Actions action;
		private bool alreadyScripted;
		private bool hasPrimaryKey;
		private bool needsIdentity_Insert;
		#endregion

		#region "Constructors"
		internal Table(Kernel parent, DataRow row)
		{
			SqlConnection cn;
			SqlDataReader dr;
			SqlCommand cmd;

			// =====================================================================
			// === Pre-Process                                                   ===
			// =====================================================================
			hasPrimaryKey = false;

			// === Initialize private members ======================================
			columns = new List<Column>();
			droppedColumns = new List<Column>();
			//dependencies = new List<Dependency>();
			status = Statuses.Unknown;
			action = Actions.None;
			condition = null;
			needsIdentity_Insert = false;

			// === Save the parameters =============================================
			if (parent != null)
				this.parent = parent;
			else
				throw new ArgumentNullException("The parent object cannot be null");

			primarySchema = row["PrimarySchema"].ToString();
			primaryTable = row["PrimaryTable"].ToString();
			// This information is set by RefreshArchivePolicy()
			//foreignSchema = row["ForeignSchema"].ToString();
			//foreignTable = row["ForeignTable"].ToString();

			// === Open a database connection ======================================
			cn = new SqlConnection(parent.LocalConnectionString);
			cn.Open();
			cmd = cn.CreateCommand();

			// =====================================================================
			// === Process                                                       ===
			// =====================================================================
			// === Retrieve the columns ============================================
			cmd.CommandText = String.Format("SELECT C.name AS colName, T.name AS colType, C.length AS colLength, ISNULL(C.prec,0) AS colPrecision, C.xscale AS colScale, C.isNullable, CAST((CASE (c.status & 128) WHEN 128 THEN 1 ELSE 0 END) AS BIT) AS isId FROM sysColumns C, sysTypes T WHERE C.xtype=T.xtype AND C.usertype = T.usertype AND id = OBJECT_ID('[{0}].[{1}]') ORDER BY colId", primarySchema, primaryTable);
			dr = cmd.ExecuteReader();
			while (dr.Read())
				columns.Add(new Column(this, dr));
			dr.Close();

			// === Detect the primary key(s) =======================================
			cmd.CommandText = String.Format("EXEC sp_PKeys @Table_Owner='{0}', @Table_Name = '{1}'", primarySchema, primaryTable);
			dr = cmd.ExecuteReader();
			while (dr.Read())
			{
				foreach (Column item in columns)
					if (item.Name == dr["Column_Name"].ToString())
					{
						item.IsKey = true;
						break;
					}
				foreach (Column item in columns)
					if (item.IsKey == true)
					{
						hasPrimaryKey = true;
						break;
					}
			}
			dr.Close();

			//if (hasPrimaryKey == false)
			//  System.Windows.Forms.MessageBox.Show(string.Format("No primary key has been found in the table [{0}].[{1}]:\n\nthis table cannot directly or indirectly be part of any archiving policy", primarySchema, primaryTable), Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Warning);

			// =====================================================================
			// === Prost-Process                                                 ===
			// =====================================================================
			// === Close the database connection ===================================
			cmd.Connection = null;
			cn.Close();
		}
		#endregion

		#region "Properties"
		internal string Key
		{
			get
			{
				// I changed the following line to reflect the standard notification 
				// format for a table (schema.table)
				return string.Format(Constants.TABLE_KEY_FORMAT, primarySchema, primaryTable);
			}
		}

		internal Kernel Parent
		{
			get { return parent; }
		}

		public string PrimarySchema
		{
			get { return primarySchema; }
		}

		public string PrimaryTable
		{
			get { return primaryTable; }
		}

		internal string ForeignSchema
		{
			get { return foreignSchema; }
		}

		internal string ForeignTable
		{
			get { return foreignTable; }
		}

		public List<Column> Columns
		{
			get { return columns; }
		}

		//public List<Dependency> Dependencies
		//{
		//  get { return dependencies; }
		//}

		public Statuses Status
		{
			get { return status; }
		}

		public Actions Action
		{
			get { return action; }
		}

		public string Condition
		{
			get { return condition; }
		}

		internal bool AlreadyScripted
		{
			get { return alreadyScripted; }
			set { alreadyScripted = value; }
		}

		internal bool NeedsIdentity_Insert
		{
			get { return needsIdentity_Insert; }
			set { needsIdentity_Insert = value; }
		}
		#endregion

		#region "Methods"
		internal void SetArchiveStatus(Statuses newStatus, string newCondition)
		{
			status = newStatus;
			condition = newCondition;
		}

		internal void RefreshArchiveTableInformation()
		{
			SqlConnection cn;
			SqlCommand cmd;
			SqlDataReader dr;
			SqlDataAdapter da;
			DataTable dt;
			bool found;
			string searchSchema;
			string searchTable;
			string query;

			// =====================================================================
			// === Pre-Process                                                   ===
			// =====================================================================
			// === Determine the remote table information ==========================
			searchSchema = primarySchema;
			searchTable = string.Format("{0}{1}", Constants.PFX_TABLE, PrimaryTable);

			// === Open a database connection ======================================
			cn = new SqlConnection(parent.RemoteConnectionString);
			cn.Open();
			cmd = cn.CreateCommand();
			dt = new DataTable();

			// =====================================================================
			// === Process                                                       ===
			// =====================================================================
			// === Detect if a remote table exists =================================
			query = string.Format("SELECT {1}(uid) AS tblSchema, name AS tblName FROM sysObjects WHERE xtype='U' AND name LIKE '{0}%'",
															searchTable,
															(parent.SqlVersion == SqlVersions.Sql2000 ? "USER_NAME" : "SCHEMA_NAME"));
			da = new SqlDataAdapter(query, cn);
			da.Fill(dt);
			if (dt.Rows.Count != 0)
			{
				foreignSchema = dt.Rows[0]["tblSchema"].ToString();
				foreignTable = dt.Rows[0]["tblName"].ToString();
			}
			else
			{
				foreignSchema = null;
				foreignTable = null;
			}

			// === Detect previous archiving policy on this table ==================
			if ((foreignTable != null) && (foreignTable.Length != 0))
			{
				cmd.CommandText = String.Format("SELECT C.name AS colName, T.name AS colType, C.length AS colLength, ISNULL(C.prec,0) AS colPrecision, C.xscale AS colScale, C.isNullable, CAST((CASE (c.status & 128) WHEN 128 THEN 1 ELSE 0 END) AS BIT) AS isId FROM sysColumns C, sysTypes T WHERE C.xtype=T.xtype AND C.usertype = T.usertype AND id = OBJECT_ID('[{0}].[{1}]') ORDER BY colId", foreignSchema, foreignTable);
				dr = cmd.ExecuteReader();
				while (dr.Read())
				{
					found = false;
					foreach (Column item in columns)
						if (item.Name.ToLower() == dr["colName"].ToString().ToLower())
						{
							item.Status = Statuses.Installed;
							found = true;
							break;
						}
					if (found == false)
						droppedColumns.Add(new Column(this, dr));
				}
				dr.Close();
				//status = Statuses.Installed; ==> The existence of the table does not impact the status!!!
			}

			// =====================================================================
			// === Prost-Process                                                 ===
			// =====================================================================
			// === Set "unknown" status to "uninstall" =============================
			if (status == Statuses.Unknown) status = Statuses.NotInstalled;

			// === Close the database connection ===================================
			cmd.Connection = null;
			cn.Close();
		}

		internal static string GetKey(string schema, string name)
		{
			return string.Format(Constants.TABLE_KEY_FORMAT, schema, name).ToLower();
		}

		internal void SetStatus(Statuses newStatus)
		{
			if (status != newStatus)
			{
				if (status != Statuses.Unknown)
				{
					status = newStatus;
					SetAction(action, condition);
				}
				else
					throw new InvalidOperationException(string.Format("Cannot set status [{0}]", newStatus.ToString()));
			}
		}

		internal void SetAction(Actions newAction)
		{
			SetAction(newAction, null, true);
		}

		public void SetAction(Actions newAction, string newCondition)
		{
			SetAction(newAction, newCondition, false);
		}

		private void SetAction(Actions newAction, string newCondition, bool force)
		{
			if (action != newAction || Condition != newCondition)
			{
				if (status == Statuses.Unknown)
					throw new InvalidOperationException(string.Format("Cannot set action due to its status [{0}]", status.ToString()));

				switch (newAction)
				{
					case Actions.None:
						action = newAction;
						break;
					case Actions.Install:
						if (hasPrimaryKey == false)
							MessageBox.Show(string.Format("The table [{0}].[{1}] cannot be archived since it has not primary key.", primarySchema, primaryTable), Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
						else
							if (status == Statuses.NotInstalled || Condition != newCondition)
								if (SetCondition(newCondition, force) == true)
									action = newAction;
								else
									MessageBox.Show(string.Format("Cannot implement the action [{0}] because of the invalid condition [{1}]", newAction, newCondition), Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
							else
								MessageBox.Show(string.Format("Cannot apply the action [{0}] on the table [{1}].[{2}]: it already has the status [{3}]", newAction, PrimarySchema, PrimaryTable, status), Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
						break;
					case Actions.Uninstall:
						if (status == Statuses.Installed)
						{
							action = newAction;
							condition = null;
						}
						else
							throw new InvalidOperationException(string.Format("Cannot apply the action [{0}] on the table [{1}].[{2}]: it already has the status [{3}]", newAction, PrimarySchema, PrimaryTable, status));
						break;
					default:
						throw new InvalidOperationException(string.Format("Unmanaged action [{0}]", newAction));
				}

				// === Update colummns ===============================================
				foreach (Column item in columns)
					try
					{
						item.SetAction(action);
					}
					catch
					{
					}

			}
		}

		private bool SetCondition(string value, bool force)
		{
			bool rc;
			SqlConnection cn;
			SqlCommand cmd;
			object dummy;

			// =====================================================================
			// === Pre-Process                                                   === 
			// =====================================================================
			// === Initialize the variables ========================================
			rc = false;

			if (force == false)
			{
				// === Open a database connection ======================================
				cn = new SqlConnection(parent.LocalConnectionString);
				cn.Open();
				cmd = cn.CreateCommand();

				// =====================================================================
				// === Process                                                       ===
				// =====================================================================
				if (value != null)
				{
					try
					{
						cmd.CommandText = string.Format("SELECT COUNT(*) FROM [{0}].[{1}] WHERE {2}", primarySchema, primaryTable, value);
						dummy = cmd.ExecuteScalar();
						condition = value;
						rc = true;

					}
					catch
					{
					}
				}

				// =====================================================================
				// === Post-Process                                                  === 
				// =====================================================================
				cmd.Connection = null;
				cn.Close();
			}
			else
			{
				condition = value;
				rc = true;
			}

			return rc;
		}

		internal void GetScript(List<string> script, bool isLocal)
		{
			// =====================================================================
			// === PRE-PROCESS                                                   ===
			// =====================================================================

			// =====================================================================
			// === PROCESS                                                       ===
			// =====================================================================
			// === Process Archive Table ===========================================
			if (isLocal == false) GetScriptTable(script);

			// === Script the view =================================================
			GetScriptView(script);

			// === Script the trigger ==============================================
			if (isLocal == true)
				GetScriptProcedure(script, isLocal);
			else
				GetScriptProcedure(script, isLocal);


			// ======================================================================
			// === POST-PROCESS                                                   ===
			// ======================================================================
			// If I went here but dut not do anything, don't mark it as already scripted
			// This will allow reentrance caused bu dependencies
			alreadyScripted = (((action == Actions.None) && (status == Statuses.NotInstalled)) ? false : true);

			// === Generate Scripts for dependent objects ===========================
			//if ((action == Actions.Install) || ((action == Actions.None) && (status == Statuses.Installed)))
			//  foreach (Dependency item in parent.Dependencies)
			//    if ((item.PrimarySchema == primarySchema) && (item.PrimaryTable == primaryTable) && (item.ForeignObject.alreadyScripted == false))
			//    {
			//      parent.Tables[item.ForeignTable].SetAction(Actions.Install);
			//      parent.Tables[item.ForeignTable].GetScript(script, isLocal);
			//    }
		}

		private void GetScriptView(List<string> script)
		{
			bool shouldInstall;
			StringBuilder rc;
			StringBuilder fields;

			string objectSchema;
			string objectName;

			string localDatabase;
			string localSchema;
			string localTable;

			string remoteDatabase;
			string remoteSchema;
			string remoteTable;

			// =====================================================================
			// === Pre-Process
			// =====================================================================
			shouldInstall = false;

			rc = new StringBuilder();
			fields = new StringBuilder();

			objectSchema = primarySchema;
			objectName = string.Format("{0}{1}", Constants.PFX_VIEW, primaryTable);

			localDatabase = parent.LocalDatabase;
			localSchema = primarySchema;
			localTable = primaryTable;

			remoteDatabase = parent.RemoteDatabase;
			remoteSchema = primarySchema;
			remoteTable = string.Format("{0}{1}", Constants.PFX_TABLE, primaryTable);
			// =====================================================================
			// === Process                                                       ===
			// =====================================================================
			switch (action)
			{
				case Actions.None:
					if (status == Statuses.Installed)
						shouldInstall = true;
					break;
				case Actions.Install:
					shouldInstall = true;
					break;
				case Actions.Uninstall:
					break;
			}

			if ((parent.CleanUp == true) || (shouldInstall == true))
			{
				AddInfoView(script);
				Constants.AddScriptItem(script, Constants.GetScriptDropObject(objectSchema, objectName, ObjectTypes.VIEW));
				Constants.AddScriptItem(script, "\nGO");
			}

			if (shouldInstall == true)
			{
				foreach (Column item in columns)
					if (item.IsTypeStrict() == true)
						fields.AppendFormat("{0},", item.Name);
					else if (item.Type == "text")
						fields.AppendFormat("CAST({0} AS VARCHAR(MAX)) AS {0},", item.Name);


				fields.Remove(fields.Length - 1, 1);

				rc.AppendFormat("CREATE VIEW [{0}].[{1}] AS ", objectSchema, objectName);
				rc.AppendFormat("\nSELECT {4}, 0 AS [{0}] FROM [{1}].[{2}].[{3}]", Constants.SCHEMA, localDatabase, localSchema, localTable, fields.ToString());
				rc.AppendFormat("\nUNION");
				rc.AppendFormat("\nSELECT {4}, 1 AS [{0}] FROM [{1}].[{2}].[{3}]", Constants.SCHEMA, remoteDatabase, remoteSchema, remoteTable, fields.ToString());
				rc.AppendFormat("\n");
				Constants.AddScriptItem(script, rc.ToString());
				Constants.AddScriptItem(script, "\nGO");
			}

			// =====================================================================
			// === Post-Process                                                  ===
			// =====================================================================
		}

		private void GetScriptTable(List<string> script)
		{
			StringBuilder rc;

			string objectSchema;
			string objectTable;

			bool shouldAlterAdd;
			bool shouldAlterDrop;

			// =====================================================================
			// === Pre-Process
			// =====================================================================
			rc = new StringBuilder();
			objectSchema = primarySchema;
			objectTable = string.Format("{0}{1}", Constants.PFX_TABLE, primaryTable);
			shouldAlterAdd = false;
			shouldAlterDrop = false;

			// =====================================================================
			// === Process                                                       ===
			// =====================================================================

			// =====================================================================
			// === Post-Process                                                  ===
			// =====================================================================
			switch (action)
			{
				case Actions.None:
				case Actions.Install:
					if ((foreignTable != null) && (foreignTable.Length != 0)) // (status == Statuses.Installed)
					{
						// === ALTER TABLE if the system is installed ====================
						foreach (Column item in columns)
							if (item.Status == Statuses.NotInstalled) shouldAlterAdd = true;

						// === Verify if new columns need to be added ====================
						if (shouldAlterAdd == true)
						{
							AddInfoTable(script);
							rc.AppendFormat("ALTER TABLE [{0}].[{1}] ADD", objectSchema, objectTable);
							foreach (Column item in columns)
								if (item.Status == Statuses.NotInstalled)
									rc.AppendFormat("\n   {0},", item.ToString());
							rc.Remove(rc.Length - 1, 1);
							rc.AppendLine(";");
						}

						// === Verify if existing columns need to be dropped =============
						shouldAlterDrop = (droppedColumns.Count != 0 ? true : false);
						if (shouldAlterDrop == true)
						{
							AddInfoTable(script);
							rc.AppendFormat("ALTER TABLE [{0}].[{1}] DROP COLUMN ", objectSchema, objectTable);
							foreach (Column item in droppedColumns)
								rc.AppendFormat("\n   {0},", item.Name);
							rc.Remove(rc.Length - 1, 1);
							rc.AppendLine(";");
						}
					}
					else
					{
						// === CREATE TABLE ================================================
						AddInfoTable(script);
						rc.AppendFormat("CREATE TABLE [{0}].[{1}] (", objectSchema, objectTable);
						foreach (Column item in columns)
							if (item.Action == Actions.Install)
								rc.AppendFormat("\n   {0},", item.ToString());
						rc.Remove(rc.Length - 1, 1);
						rc.AppendLine(");");
					}
					break;
				case Actions.Uninstall:
					// === DROP TABLE ==================================================
					AddInfoTable(script);
					if (parent.KeepData == false)
						rc.AppendLine(Constants.GetScriptDropObject(objectSchema, objectTable, ObjectTypes.TABLE));
					break;
			}

			if (rc.Length != 0)
			{
				Constants.AddScriptItem(script, rc.ToString());
				Constants.AddScriptItem(script, "\nGO");
			}
		}

		private void GetScriptProcedure(List<string> script, bool isLocal)
		{
			bool shouldInstall;
			StringBuilder rc;

			StringBuilder fields;
			bool isForeignTable; // True when this table depends on others
			bool isPrimaryTable; // True when other tables depend on this table
			//bool useWhereKeyword;
			string whereClause;

			string objectSchema;
			string objectName;

			string localSchema;
			string localTable;
			string localCondition;

			string remoteDatabase;
			string remoteSchema;
			string remoteTable;

			// =====================================================================
			// === Pre-Process                                                   ===
			// =====================================================================
			// === Initialize variables ============================================
			rc = new StringBuilder();
			if (isLocal == true)
			{
				localSchema = primarySchema;
				localTable = primaryTable;
				localCondition = condition;
				remoteDatabase = parent.RemoteDatabase;
				remoteSchema = primarySchema;
				remoteTable = string.Format("{0}{1}", Constants.PFX_TABLE, primaryTable);
			}
			else
			{
				localSchema = primarySchema;
				localTable = string.Format("{0}{1}", Constants.PFX_TABLE, primaryTable);
				localCondition = (((condition == null) || (condition.Length == 0)) ? null : (string.Format("NOT ({0})", condition)));
				remoteDatabase = parent.LocalDatabase;
				remoteSchema = primarySchema;
				remoteTable = primaryTable;
			}

			objectSchema = primarySchema;
			objectName = string.Format("{0}{1}", Constants.PFX_PROCEDURE, primaryTable);

			shouldInstall = false;
			isForeignTable = false;
			isPrimaryTable = false;

			fields = new StringBuilder();

			// === Determine if the stored procedure must be installed =============
			switch (action)
			{
				case Actions.None:
					if (status == Statuses.Installed)
						shouldInstall = true;
					break;
				case Actions.Install:
					shouldInstall = true;
					break;
				case Actions.Uninstall:
					break;
			}

			// =====================================================================
			// === Process                                                       ===
			// =====================================================================
			// === When clean up is forced, drop any previous procedure ============
			#region Clean
			if ((parent.CleanUp == true) || (shouldInstall == true))
			{
				AddInfoProcedure(script);
				Constants.AddScriptItem(script, Constants.GetScriptDropObject(objectSchema, objectName, ObjectTypes.PROCEDURE));
				Constants.AddScriptItem(script, "\nGO");
			}
			#endregion

			// === Create the stored procedure =====================================
			if (shouldInstall == true)
			{
				whereClause = BuildWhereClause("\n\t\t\t");

				foreach (Column item in columns)
					// Ignore timestamp columns, because they can't be copied
					if (!item.Type.Equals("timestamp"))
						fields.AppendFormat("{0},", item.Name);
				fields.Remove(fields.Length - 1, 1);

				#region Anfang + Definitionen
				rc.AppendFormat("CREATE PROCEDURE [{0}].[{1}] ", objectSchema, objectName);
				rc.AppendFormat(string.Format("\n\t@EntryPoint BIT"));

				// When not called has an entry point, must pass foreign key values
				foreach (Dependency item in parent.Dependencies)
				{
					if ((item.ForeignSchema == primarySchema) && (item.ForeignTable == primaryTable))
					{
						rc.AppendFormat(",\n\t@root_{0}", item.ForeignKeyToString());
						isForeignTable = true;
					}
					if ((item.PrimarySchema == primarySchema) && (item.PrimaryTable == primaryTable))
						isPrimaryTable = true;
				}
				rc.AppendFormat("\nAS");
				rc.AppendFormat("\nBEGIN");
				// Declare variables representing the primary key(s)
				rc.AppendFormat("\nDECLARE @ReturnCode INT;");
				foreach (Column item in columns)
					if (item.IsKey == true)
						rc.AppendFormat("\nDECLARE @{0};", item.ToString2());
				rc.AppendFormat("\n");

				// === Step 0: Begin a transaction ===================================
				rc.AppendFormat("\n\t-------------------------------------");
				rc.AppendFormat("\n\t-- Step 0: Start a new transaction --");
				rc.AppendFormat("\n\t-------------------------------------");
				rc.AppendFormat("\n\tSET NOCOUNT ON;");
				rc.AppendFormat("\n\tBEGIN TRANSACTION");
				// === Step 0: Begin a transaction ===================================
				#endregion

				rc.AppendFormat("\n");

				// === Step 1: Put main records in the archive table =================
				#region Insert
				rc.AppendFormat("\n\t---------------------------------------------------");
				rc.AppendFormat("\n\t-- Step 1: Put main records in the archive table --");
				rc.AppendFormat("\n\t---------------------------------------------------");
				rc.AppendFormat("\n\tIF (@EntryPoint = 1)");
				rc.AppendFormat("\n\tBEGIN");
				if ((localCondition == null) || (localCondition.Length == 0))
					// When install is forced, this stored procedure cannot be called as an entry point
					rc.AppendFormat("\n\t\tRAISERROR ('The procedure [{0}].[{1}] can not be called as an entry point (no condition declared)', 18, 127);", objectSchema, objectName);
				else
				{
					// Get all records meeting the archive conditions
					// If the table contains an id column, a identity insert is needed
					if ((isLocal == false) && (NeedsIdentity_Insert == true))
						rc.AppendFormat("\n\t\tSET IDENTITY_INSERT [{0}].[{1}].[{2}] ON", remoteDatabase, remoteSchema, remoteTable);
					rc.AppendFormat("\n\t\tINSERT INTO [{0}].[{1}].[{2}] ({3})", remoteDatabase, remoteSchema, remoteTable, fields);
					rc.AppendFormat("\n\t\t\tSELECT {0}", fields);
					rc.AppendFormat("\n\t\t\tFROM [{0}].[{1}]", localSchema, localTable);
					rc.AppendFormat("\n\t\t\t{0}", Constants.TAG_OPEN);
					rc.AppendFormat("\n\t\t\tWHERE {0}", localCondition);
					rc.AppendFormat("\n\t\t\t{0}", Constants.TAG_CLOSE);
					if ((isLocal == false) && (NeedsIdentity_Insert == true))
						rc.AppendFormat("\n\t\tSET IDENTITY_INSERT [{0}].[{1}].[{2}] OFF", remoteDatabase, remoteSchema, remoteTable);
				}
				rc.AppendFormat("\n\tEND");
				rc.AppendFormat("\n\tELSE");
				rc.AppendFormat("\n\tBEGIN");

				if (isForeignTable == false)
					rc.AppendFormat("\n\t\tRAISERROR ('The procedure [{0}].[{1}] can not be called as a dependent entity (no dependencies found)', 18, 127);", objectSchema, objectName);
				else
				{
					if ((isLocal == false) && (NeedsIdentity_Insert == true))
						rc.AppendFormat("\n\t\tSET IDENTITY_INSERT [{0}].[{1}].[{2}] ON", remoteDatabase, remoteSchema, remoteTable);
					rc.AppendFormat("\n\t\tINSERT INTO [{0}].[{1}].[{2}] ({3})", remoteDatabase, remoteSchema, remoteTable, fields);
					rc.AppendFormat("\n\t\t\tSELECT {0}", fields);
					rc.AppendFormat("\n\t\t\tFROM [{0}].[{1}]", localSchema, localTable);

					rc.AppendFormat(whereClause);
					//useWhereKeyword = true;
					//foreach (Dependency item in parent.Dependencies)
					//  if ((item.ForeignSchema == primarySchema) && (item.ForeignTable == primaryTable))
					//  {
					//    rc.AppendFormat("\n\t\t\t{0} {1}=@root_{1}", (useWhereKeyword == true ? "WHERE" : "AND"), item.ForeignKey);
					//    useWhereKeyword = false;
					//  }

					if ((isLocal == false) && (NeedsIdentity_Insert == true))
						rc.AppendFormat("\n\t\tSET IDENTITY_INSERT [{0}].[{1}].[{2}] OFF", remoteDatabase, remoteSchema, remoteTable);
				}

				rc.AppendFormat("\n\tEND");
				// === Step 1: Put main records in the archive table =================
				#endregion

				rc.AppendFormat("\n");

				// === Step 2: Archive dependent objects =============================
				#region Dependent
				if (isPrimaryTable == true)
				{
					rc.AppendFormat("\n\t---------------------------------------");
					rc.AppendFormat("\n\t-- Step 2: Archive dependent objects --");
					rc.AppendFormat("\n\t---------------------------------------");
					// --- IF clause -----------------------------------------------------
					rc.AppendFormat("\n\tIF (@EntryPoint = 1)");
					rc.AppendFormat("\n\tBEGIN");
					if ((localCondition == null) || (localCondition.Length == 0))
						// When install is forced, this stored procedure cannot be called as an entry point
						rc.AppendFormat("\n\t\tRAISERROR ('The procedure [{0}].[{1}] can not be called as an entry point (no condition declared)', 18, 127);", objectSchema, objectName);
					else
					{
						// Get all records meeting the archive conditions
						rc.AppendFormat("\n\t\tDECLARE myCursor_{0} CURSOR FAST_FORWARD READ_ONLY FOR", localTable);
						rc.AppendFormat("\n\t\t\tSELECT ");
						foreach (Column item in columns)
							if (item.IsKey)
								rc.AppendFormat("{0},", item.Name);
						rc.Remove(rc.Length - 1, 1);

						rc.AppendFormat("\n\t\t\tFROM  [{0}].[{1}]", localSchema, localTable);
						rc.AppendFormat("\n\t\t\t{0}", Constants.TAG_OPEN);
						rc.AppendFormat("\n\t\t\tWHERE {0}", localCondition);
						rc.AppendFormat("\n\t\t\t{0}", Constants.TAG_CLOSE);
					}
					rc.AppendFormat("\n\tEND");
					rc.AppendFormat("\n\tELSE");
					rc.AppendFormat("\n\tBEGIN");
						// Get all records depending on the foreign record

						rc.AppendFormat("\n\t\tDECLARE myCursor_{0} CURSOR FAST_FORWARD READ_ONLY FOR", localTable);
						rc.AppendFormat("\n\t\t\tSELECT ");
						foreach (Column item in columns)
						    if (item.IsKey)
						        rc.AppendFormat("{0},", item.Name);
						rc.Remove(rc.Length - 1, 1);

						rc.AppendFormat("\n\t\t\tFROM  [{0}].[{1}]", localSchema, localTable);

						rc.AppendFormat(whereClause);
					//useWhereKeyword = true;
					//foreach (Dependency item in parent.Dependencies)
					//  if ((item.ForeignSchema == primarySchema) && (item.ForeignTable == primaryTable))
					//  {
					//    rc.AppendFormat("\n\t\t\t{0} {1}=@root_{1}", (useWhereKeyword == true ? "WHERE" : "AND"), item.ForeignKey);
					//    useWhereKeyword = false;
					//  }

					rc.AppendFormat("\n\tEND");
					// --- IF clause -----------------------------------------------------

					rc.AppendFormat("\n");

					// --- MAIN LOOP -----------------------------------------------------
					rc.AppendFormat("\n\tOPEN myCursor_{0};", localTable);
					rc.AppendFormat("\n\tFETCH NEXT FROM myCursor_{0} INTO ", localTable);
					foreach (Column item in columns)
						if (item.IsKey == true)
							rc.AppendFormat("@{0},", item.Name);
					rc.Remove(rc.Length - 1, 1);
					rc.AppendFormat(";");

					rc.AppendFormat("\n\tWHILE (@@FETCH_STATUS = 0)");
					rc.AppendFormat("\n\tBEGIN");

					// --- EXEC
					// When not called has an entry point, must pass foreign key values
					foreach (Dependency item in parent.Dependencies)
						if ((item.PrimarySchema == primarySchema) && (item.PrimaryTable == primaryTable))
						{
							objectName = string.Format("{0}{1}", Constants.PFX_PROCEDURE, primaryTable);

							rc.AppendFormat("\n\t\tEXEC @ReturnCode = {0}{1} {2}", Constants.PFX_PROCEDURE, item.ForeignTable, GetExecParameters(item.ForeignSchema, item.ForeignTable, false));
							rc.AppendFormat("\n\t\tIF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback");
						}

					// --- Fetch Next
					rc.AppendFormat("\n\t\tFETCH NEXT FROM myCursor_{0} INTO ", localTable);
					foreach (Column item in columns)
						if (item.IsKey == true)
							rc.AppendFormat("@{0},", item.Name);
					rc.Remove(rc.Length - 1, 1);
					rc.AppendFormat(";");

					rc.AppendFormat("\n\tEND");

					// --- Close cursor
					rc.AppendFormat("\n\tCLOSE myCursor_{0}", localTable);
					rc.AppendFormat("\n\tDEALLOCATE myCursor_{0}", localTable);


					// --- MAIN LOOP ---------------------------------------------------
				}
				// === Step 2: Archive dependent objects =============================
				#endregion

				rc.AppendFormat("\n");

				#region Delete
				// === Step 3: Delete archived records from the primary table ========
				rc.AppendFormat("\n\t------------------------------------------------------------");
				rc.AppendFormat("\n\t-- Step 3: Delete archived records from the primary table --");
				rc.AppendFormat("\n\t------------------------------------------------------------");
				rc.AppendFormat("\n\tIF (@EntryPoint = 1)");
				rc.AppendFormat("\n\tBEGIN");
				if ((localCondition == null) || (localCondition.Length == 0))
					// When install is forced, this stored procedure cannot be called as an entry point
					rc.AppendFormat("\n\t\tRAISERROR ('The procedure [{0}].[{1}] can not be called as an entry point (no condition declared)', 18, 127);", objectSchema, objectName);
				else
				{
					// Get all records meeting the archive conditions
					rc.AppendFormat("\n\t\tDELETE FROM [{0}].[{1}]", localSchema, localTable);
					rc.AppendFormat("\n\t\t\tWHERE {0}", localCondition);
				}
				rc.AppendFormat("\n\tEND");
				rc.AppendFormat("\n\tELSE");
				rc.AppendFormat("\n\tBEGIN");

				if (isForeignTable == false)
					rc.AppendFormat("\n\t\tRAISERROR ('The procedure [{0}].[{1}] can not be called as a dependent entity (no dependencies found)', 18, 127);", objectSchema, objectName);
				else
				{
					rc.AppendFormat("\n\t\tDELETE FROM [{0}].[{1}]", localSchema, localTable);

					rc.AppendFormat(whereClause);
					//useWhereKeyword = true;
					//foreach (Dependency item in parent.Dependencies)
					//  if ((item.ForeignSchema == primarySchema) && (item.ForeignTable == primaryTable))
					//  {
					//    rc.AppendFormat("\n\t\t\t{0} {1}=@root_{1}", (useWhereKeyword == true ? "WHERE" : "AND"), item.ForeignKey);
					//    useWhereKeyword = false;
					//  }
				}

				rc.AppendFormat("\n\tEND");
				// === Step 3: Delete archived records from the primary table ========
				#endregion

				rc.AppendFormat("\n");

				#region Ende
				// === Step 9: Commit or roll back the transaction ===================
				rc.AppendFormat("\n\t-------------------------------------------------");
				rc.AppendFormat("\n\t-- Step 9: Commit or roll back the transaction --");
				rc.AppendFormat("\n\t-------------------------------------------------");
				rc.AppendFormat("\n\tIF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback");
				rc.AppendFormat("\n");
				rc.AppendFormat("\n\tCOMMIT TRANSACTION");
				rc.AppendFormat("\n\tGOTO EndProcedure");
				rc.AppendFormat("\n");
				rc.AppendFormat("\nQuitWithRollback:");
				rc.AppendFormat("\n\tIF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION");
				rc.AppendFormat("\n");
				rc.AppendFormat("\nEndProcedure:");
				rc.AppendFormat("\nEND");
				// === Step 9: Commit or roll back the transaction ===================
				rc.AppendFormat("\n");
				#endregion
				Constants.AddScriptItem(script, rc.ToString());
				Constants.AddScriptItem(script, "\nGO");
			}

			// =====================================================================
			// === Post-Process                                                  ===
			// =====================================================================

		}

		private void AddInfoTable(List<string> script)
		{
			string info;

			info = String.Format("--- Table: [{0}].[{1}].[{2}]", parent.LocalDatabase, primarySchema, primaryTable);
			script.Add(String.Format("{0}{1}", info, new string('-', 78 - info.Length)));
		}

		private void AddInfoView(List<string> script)
		{
			string info;
			string objectSchema;
			string objectName;

			objectSchema = primarySchema;
			objectName = string.Format("{0}{1}", Constants.PFX_VIEW, primaryTable);

			info = String.Format("---View: [{0}][{1}]", objectSchema, objectName);
			script.Add(String.Format("{0}{1}", info, new string('-', 78 - info.Length)));
		}

		private void AddInfoProcedure(List<string> script)
		{
			string info;

			info = String.Format("---Procedure: [{0}{1}]", Constants.PFX_PROCEDURE, primaryTable);
			script.Add(String.Format("{0}{1}", info, new string('-', 78 - info.Length)));
		}

		internal Statuses GetResultingStatus()
		{
			Statuses rc;

			switch (action)
			{
				// -------------------------------------------------------------------
				case Actions.None:
					switch (status)
					{
						case Statuses.NotInstalled:
							rc = Statuses.NotInstalled;
							break;
						case Statuses.Installed:
							rc = Statuses.Installed;
							break;
						case Statuses.Unknown:
						default:
							throw new Exception(string.Format("Invalid status value [{0}] found.", status));
					}
					break;
				// -------------------------------------------------------------------
				case Actions.Install:
					rc = Statuses.Installed;
					// The following code should enable to check inconsistencies occuring
					// when setting up the archiving policies
					//switch (status)
					//{
					//  case Statuses.NotInstalled:
					//    rc = Statuses.Installed;
					//    break;
					//  case Statuses.Installed:
					//    // This should not be possible
					//    rc = Statuses.Installed;
					//    break;
					//  case Statuses.Unknown:
					//    throw new Exception(string.Format("Invalid status value [{0}] found.", status));
					//}
					break;
				// -------------------------------------------------------------------
				case Actions.Uninstall:
					rc = Statuses.NotInstalled;
					// The following code should enable to check inconsistencies occuring
					// when setting up the archiving policies
					//switch (status)
					//{
					//  case Statuses.NotInstalled:
					//    // This should not be possible
					//    rc = Statuses.NotInstalled;
					//    break;
					//  case Statuses.Installed:
					//    rc = Statuses.NotInstalled;
					//    break;
					//  case Statuses.Unknown:
					//    throw new Exception(string.Format("Invalid status value [{0}] found.", status));
					//}
					break;
				default:
					throw new Exception(string.Format("The action [{0}] is unmanaged.", action));
			}

			return rc;
		}

		/// <summary>
		/// Let's consider the following case: the table tblOrderDetail is an association
		/// between tblOrder and tblProduct. The associated stored procedure can be called
		/// in two ways: as an entry point, or as a dependent object.
		/// 
		/// When called as a dependent object, it can be called from two different entry
		/// points: 
		/// - SP managing tblOrder: in this case, it will pass a value for fkordId
		/// - SP managing tblProduct: in this case, it will pass a value for prdId
		/// 
		/// ...THUS...
		/// 
		/// The SP managing tblOrderDetail will never receive a value for both rootfkordId 
		/// and rootfkprdId: The best way to manage that is to handle that issue in the
		/// WHERE clause:
		///			WHERE ((fkordId = @rootfkordId) OR (fkprdId = @rootfkprdId))
		/// 
		/// This function will generate the WHERE clause base on the following pattern, 
		/// by grouping the filters by table:
		/// WHERE ((Table1.Key1 = @rootKeyA) AND (Table1.Key2 = @rootKeyB))
		/// OR ((Table2.Key1 = @rootKeyC))
		/// OR ((... = ...) AND (... = ...) AND (... = ...))
		/// </summary>
		/// <returns></returns>
		private string BuildWhereClause(string indent)
		{
			StringBuilder rc;
			SortedList<string, StringBuilder> parts; // Key is the (OLD: table) name of the dependency
			StringBuilder part;
			string key;
			bool useWhere;

			// =====================================================================
			// === Pre-Process                                                   ===
			// =====================================================================
			parts = new SortedList<string, StringBuilder>();
			rc = new StringBuilder();
			useWhere = true;

			// =====================================================================
			// === Process                                                       === 
			// =====================================================================
			foreach (Dependency dep in parent.Dependencies)
				if ((dep.ForeignSchema == primarySchema) && (dep.ForeignTable == primaryTable))
				{
					key = string.Format("{0}.{1}", dep.PrimarySchema, dep.PrimaryTable);
					if (parts.ContainsKey(key) == true)
					{
						part = parts[key];
						part.AppendFormat(" AND ({0}=@root_{0})", dep.ForeignKey);
					}
					else
					{
						part = new StringBuilder();
						part.AppendFormat("(({0}=@root_{0})", dep.ForeignKey);
						parts.Add(key, part);
					}
				}

			// =====================================================================
			// === Post-Process                                                  ===
			// =====================================================================
			// === Build the resulting string ======================================
			foreach (StringBuilder item in parts.Values)
			{
				rc.AppendFormat("{0}{1}{2})", indent, (useWhere == true ? "WHERE " : "\tOR "), item);
				useWhere = false;
			}

			key = rc.ToString();
			return rc.ToString();
		}

		internal string GetExecParameters(string calledSchema, string calledTable, bool asEntryPoint)
		{
			StringBuilder rc;

			// =====================================================================
			// === Pre-Process                                                   ===
			// =====================================================================
			rc = new StringBuilder();
			rc.AppendFormat("@EntryPoint={0}", (asEntryPoint == true ? "1" : "0"));

			// =====================================================================
			// === Process                                                       === 
			// =====================================================================
			foreach (Dependency dep in parent.Dependencies)
				if ((dep.ForeignSchema == calledSchema) && (dep.ForeignTable == calledTable))
					if ((dep.PrimarySchema == primarySchema) && (dep.PrimaryTable == primaryTable))
						rc.AppendFormat(",@root_{0}=@{1}", dep.ForeignKey, dep.PrimaryKey);
					else
						rc.AppendFormat(",@root_{0}=NULL", dep.ForeignKey);

			// =====================================================================
			// === Post-Process                                                  ===
			// =====================================================================
			return rc.ToString();
		}
		#endregion
	}
}